package org.cnbi.web.controller;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.cnbi.entity.Menu;
import org.cnbi.entity.Subject;
import org.cnbi.service.ITableService;
import org.cnbi.utils.date.DateUtil;
import org.cnbi.utils.expression.ExpressionParserUtil;
import org.cnbi.utils.pojo.DisplayBean;
import org.cnbi.utils.pojo.SqlGenerateBean;
import org.cnbi.utils.pojo.SqlParamsBean;
import org.cnbi.utils.pojo.SubjectSqlBean;
import org.cnbi.utils.pojo.UtilDim;
import org.cnbi.utils.string.StringUtil;
import org.cnbi.utils.tools.ActionConstants;
import org.cnbi.utils.tools.Constants;
import org.cnbi.utils.tools.GlobalConstants;
import org.cnbi.utils.tools.MenuConstants;
import org.cnbi.utils.tools.SymbolConstants;
import org.cnbi.web.controller.base.BaseController;
import org.cnbi.web.poi.exc.entity.ExcelTitle;
import org.cnbi.web.utils.BrowserUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
/**
 * @author 龚佳新
 * @date 2014年4月17日下午12:47:04
 * @cnbi-web
 */
@Controller
@RequestMapping("/table")
public class TableController extends BaseController {

	private static final String tableDataKey = "listTableDatas";
	/**
	 * 资产负债表的Id
	 */
	private static final String zcfzbId = "1";
	
	
	
	@Autowired
	private ITableService tableService;

	@RequestMapping(value = "/index")
	public ModelAndView main(HttpServletResponse response) {
		ModelAndView view = firstNavHandle(MenuConstants.TABLE, response);
		table(view);
		//zcfzbId.startsWith(prefix)
		return view;
	}
    /**
     * 获取查询报表的所有列项目
     * @param scode
     * @return
     */
//	private String[] getQueryFact(String scode) {
//		@SuppressWarnings("unchecked")
//		List<Menu> list = (List<Menu>) getSession().getAttribute(tableDataKey);
//		for (Menu menu : list) {
//			if (menu.getScode().equals(scode)) {
//				return menu.getState().split("\\" + SymbolConstants.COMMON);
//			}
//		}
//		return null;
//	}
	/*private String[] getQueryFactFields(String scode) {
		String[] arr = getQueryFact(scode);
		String[] fields = new String[arr.length+1];
		fields[0] = Constants.ITEMNAME;
		for (int i = 1; i < arr.length+1; i++) {
			fields[i] = arr[i-1].split("\\" + SymbolConstants.UNDERLINE)[1];
		}
		return fields;
	}*/
	/**
	 * 加载二级菜单页面
	 * @param view
	 */
	private void table(ModelAndView view) {
		@SuppressWarnings("unchecked")
		Map<Subject,List<UtilDim>> map = (Map<Subject,List<UtilDim>>)getServletContext().getAttribute(tableDataKey);
		if (null == map){
			String []dim = GlobalConstants.subjctHelDim;
			   map = new HashMap<Subject,List<UtilDim>>();
			for (int i=0,len =dim.length;i<len;i++){
				List<UtilDim> dimList = (List<UtilDim>)getDimListByKey(dim[i]);
				List<UtilDim> list = new ArrayList<UtilDim>();
				for(UtilDim table:dimList){// &&!table.getScode().equals("18")
					if(table.getLevel() == 1){
						table.setLevel(2);
						table.setSpcode(MenuConstants.TABLE);
						table.setField(dim[i]+"_"+GlobalConstants.SUBJECT[i]);
						list.add(table);
						
					}
				}
				map.put(getSubjectSqlBeanByKey(GlobalConstants.SUBJECT[i]).getSubject(),list);
			}
			getServletContext().setAttribute(tableDataKey, map);
		}
		
		view.addObject("listDatas", map);// imageSubfix
	}

	/**
	 * 响应二级菜单页面
	 * 
	 * @param param
	 * @param menu
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping(value = "/" + MenuConstants.LEVELTWO)
	public ModelAndView levelTwo(SqlParamsBean param, Menu menu, HttpServletRequest request, HttpServletResponse response) {
		//if(menu.getLevel() == 2 && menu.getScode().equals("14_tableMonth")){
			//param.setPeriod("201003");
	//	}
		ModelAndView view = secondNavHandle(menu, response);
		Object datas = queryTable(view, menu, param);
		view.addObject(Constants.DATAS, datas);
		//view.addObject("pageBeanlevelTwo----------", menu);
		return view;
	}

	/**
	 * 查询报表数据
	 * @param view
	 * @param menu
	 * @return
	 */
	private Object queryTable(ModelAndView view, Menu menu, SqlParamsBean param) {
		SqlGenerateBean bean = getSqlBean(menu, param);
		dimMapHandle(view, param, menu);
		return queryTableDataUnitHandle(bean,param);
	}

	/**
	 * @param menu
	 * @param param
	 * @param arr
	 * @return
	 */
	private SqlGenerateBean getSqlBean(Menu menu, SqlParamsBean param) {
		String [] arr = getDimHelpArr(menu.getScode());
		String itemDim = param.getDimarr()[0];
		SubjectSqlBean subject = getSubjectSqlBeanByKey(param.getSubject());
		SqlGenerateBean bean = new SqlGenerateBean(subject);
		bean.setShowDim(param.getShowdim());
		bean.setMeasureMap(subject.getMeasureMap());
		String []headList = getQueryFactFields(subject.getMeasureMap(),false);
		bean.setQueryFact(headList);
		Map<String, String> conditionMap = param.getConditionMap();
		//String itemValue = getDimValueStringByKeyAndSpcode(param.getSqlshowdim()[0], arr[0]);
		String dimName = StringUtil.toLowerCaseFirstOne(param.getShowdim()[0]);
	//	conditionMap.put(dimName, itemValue);
		//bean.setQueryFact(getQueryFact(arr[0]));
		String period = getPeriod(bean, menu, param);
		if (!period.equals("")){
			conditionMap.put("period", period);
		}
		String DW = GlobalConstants.DIMHELP;
		String orderByDim [] = {itemDim+"&ssortby"};
		bean.setOrderByDim(orderByDim);
		conditionMap.put(dimName,"(select scode from "+DW+itemDim+" where ssrccode='"+arr[0]+"')");
		
		DisplayBean display = new DisplayBean(true,true);
		display.getMap().put("nlevel", "(select nlevel from "+DW+itemDim+" where scode =dim_"+itemDim+")");
		bean.setDisplay(display);
		bean.setGroupByDim(param.getDimarr());
		param.setPeriod(period);
		bean.setConditionMap(conditionMap);
		return bean;
	}
	/**
	 * 查询报表数据与单位处理
	 * @param bean
	 * @param param
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private List<Map> queryTableDataUnitHandle(SqlGenerateBean bean,SqlParamsBean param){
		List<Map> mapList = tableService.queryListMapBean(bean.getSql(), param);
		int unit = param.getUnit();
		if(unit > 1){
			List<Map> resultMap = new ArrayList<Map>();
			Set<String> set = mapList.get(0).keySet();
			for (Map map : mapList){
				Map maps = new LinkedHashMap();
				for (String str : set){
				      Object obj = map.get(str);
				     if(!str.equals("id") && !str.equals(Constants.ITEMNAME)&& !str.equals("nlevel")){//
				    	   maps.put(Constants.ITEMNAME, map.get(Constants.ITEMNAME));
				    	   obj = ExpressionParserUtil.getConvertUnitDoubleValue(unit, map.get(str));
				     }
				     maps.put(str, obj);
				}
				resultMap.add(maps);
			}
			return resultMap;
		}else{
			return mapList;
		}
	}
	/**
	 * 获取资产负债表一年四季，上半年，下半年的期间参数数据
	 * @param period
	 * @return
	 */
	private String getSeasonData(SqlParamsBean param){
		int season = Integer.parseInt(param.getSeason());
		String period = "";
		if (season>0 && season <= 4){
			int temp =season*3;
			String m = temp+"";
			if(temp<10){
				m = "0"+temp;
			}  
			period = param.getYear()+m;
		}else if(season <= 5){
			period = param.getYear()+"06";
		}else if(season <= 6){
			period = param.getYear()+12;
		}
		return period;
	}
	private String getExcelTitle(Menu menu,SqlParamsBean param){
		String scode = menu.getScode(),reportId = scode.split("\\"+SymbolConstants.UNDERLINE)[0];
		StringBuffer titles = new StringBuffer(getTableNameById(Integer.parseInt(reportId)));
		titles.append(getCompany(param.getCompany()).getSname());
		titles.append(param.getYear()).append("年");
		if (scode.contains(SEASON)) {
			int season = Integer.parseInt(param.getSeason());
			titles.append( StringUtil.dimSeasonHelp[season-1]);
			if(season<=4){
				titles.append("季度");
			}
		}/*else if (scode.contains(YEAR)){
			titles.append(param.getYear()).append("年");
		}*/else if (scode.contains(MONTH)){
			titles.append(param.getMonth()).append("月");
		}
	    String unitarr[] = StringUtil.dimUnitHelp;
	    for (int i = 0; i < unitarr.length; i++) {
	    	String temp[] = unitarr[i].split("\\"+SymbolConstants.UNDERLINE);
	    	int unit = Integer.parseInt(temp[0]);
			if(unit == param.getUnit()){
				titles.append("单位(").append(temp[1]).append(")");
				break;
			}
		}
		titles.append("导出人("+getCurrentUser().getUsername()+")");
		return titles.toString();
		
	}
   /**
    * 月度  季度  年度数据处理
    * 季度数据 但是资产负债表是时点表除外  11
    * 年度数据 12个月的和
    * @param bean
    * @param menu
    * @param param
    * @return
    */
	private String getPeriod(SqlGenerateBean bean, Menu menu, SqlParamsBean param) {
		String period = "",scode = menu.getScode(),reportId = scode.split("\\"+SymbolConstants.UNDERLINE)[0];
		if (scode.contains(SEASON)) {//zcfzbId
			bean.setType(2);
			int season = Integer.parseInt(param.getSeason());
			/**
			 * 一年四季与半年的
			 */
			if(reportId.equals(zcfzbId)){//是资产负债表
				period = getSeasonData(param);
			}else{
				period = DateUtil.getSeasonData(Integer.parseInt(param.getYear()), season);
			}
		}else if (scode.contains(YEAR)){
			bean.setType(3);
			period = DateUtil.getYearData(Integer.parseInt(param.getYear()));
		}
		
		return period;
	}
	private String getTableNameById(int  reportId){
		String name = "";
		if(reportId ==1){
			name = "资产负债表";
		}else if(reportId ==14){
			name = "利润表";
		}else if(reportId ==16){
			name = "现金流量表";
		}
		return name;
	}
	/**
	 * 导出excel
	 * @param menu
	 * @param param
	 */
	/*@RequestMapping(value = "/" + ActionConstants.EXPORT)
	public  void exportExcel(Menu menu, SqlParamsBean param,HttpServletResponse response){
		//1:queryTableDataUnitHandle(bean, param);
		
		//2:再往excel文件里面写,亲!

	}*/
	
	
	/**
	 * 导出excel
	 * @param request
	 * @param response
	 */
	@RequestMapping(value = "/" + ActionConstants.EXPORT)
	public void exportXls(SqlParamsBean param,Menu menu, HttpServletRequest request, HttpServletResponse response) {
		response.setContentType("application/vnd.ms-excel");
		String codedFileName = null;
		OutputStream fOut = null;
		try {
			String reportIdStr = menu.getScode().split("\\"+SymbolConstants.UNDERLINE)[0];
			int reportId = Integer.parseInt(reportIdStr);
			codedFileName =getTableNameById(reportId);
			// 根据浏览器进行转码，使其支持中文文件名
			if (BrowserUtils.isIE(request)) {
				response.setHeader("content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(codedFileName, "UTF-8") + ".xls");
			} else {
				String newtitle = new String(codedFileName.getBytes("UTF-8"), "ISO8859-1");
				response.setHeader("content-disposition", "attachment;filename=" + newtitle + ".xls");
			}
			// 产生工作簿对象
			HSSFWorkbook workbook = null;
			SqlGenerateBean sqlBean = getSqlBean(menu, param);
			List<Map> dataList = queryTableDataUnitHandle(sqlBean, param);
			String name=getExcelTitle(menu, param);
			//workbook = ExcelExportUtil.exportExcel(new ExcelTitle(param.getYear()+"年"+param.getMonth()+"月份"+param.getCompany()+"公司", "导出人:"+getCurrentUser().getUsername(),codedFileName), Menu.class,  getTestList());
			 String []headList = getQueryFactFields(sqlBean.getMeasureMap(), true);
			 workbook = org.cnbi.web.poi.exc.ExcelUtil.getExcel(new ExcelTitle(name,null,codedFileName),headList,headList, dataList);
			 fOut = response.getOutputStream();
			workbook.write(fOut);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(null != fOut){
				try {
					fOut.flush();
					fOut.close();

				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
   public String [] getQueryFactFields(Map<String, String> measureMap,boolean isLabel){
	   String arr [] = new String[measureMap.size()];
	   int i = 0;
	   for (Map.Entry<String, String> entry : measureMap.entrySet()){
		   if(isLabel){
			   arr[i] = entry.getValue();
		   }else{
			   arr[i] = entry.getKey();
		   }
		  
		   i++;
	   }
	   return arr;
   }
	

}
